package com.mysql.mybatis.service.impl;

import com.mysql.mybatis.util.Connect;

import java.sql.*;
import java.util.HashMap;

/**
 * 描述: DbProxyTest
 *
 * @author : lhb
 * @date : 2019-11-27 11:54
 */
public class DbProxyTest {
    public static void main(String[] args) throws Exception {
        Connection conn;
        Statement stmt;
        try {
            conn = DriverManager.getConnection(Connect.URL, Connect.USERNAME, Connect.PASSWORD);
        } catch (Exception e) {
            System.out.println("连接数据库失败, " + e.getMessage());
            e.printStackTrace();
            return;
        }
//        暂时不作事务的处理，事务的处理在使用UDAL中会详细描述
//        conn.setAutoCommit(false);
        int result;
        String sql;
        stmt = conn.createStatement();
        System.out.println("------ insert test begin -------");

        //8应该改成插入全局系列 SEQ_TEST.nextval
        sql = "insert into user(id,user_name,description,is_deleted) values(SEQ_TEST.nextval,'test','test',1)";
        result = stmt.executeUpdate(sql);
        System.out.println("insert result: " + result);

        HashMap<String, String> map = DbProxyTest.printTableData(stmt);
        System.out.println("------ insert test end -------\n\n");


        System.out.println("------ update test begin -------");
        for (String s : map.keySet()) {
            String updateSql = "update user set description = '" + map.get(s) + s + "' where id = " + s;
            result = stmt.executeUpdate(updateSql);
            System.out.println("update result: " + result);
        }
        DbProxyTest.printTableData(stmt);
        System.out.println("------ update test end -------");

        System.out.println("------ delete test begin -------");
        stmt.executeUpdate("delete from user where id=8");
        System.out.println("------ delete test end -------");

        stmt.close();
        conn.close();
    }


    private static HashMap<String, String> printTableData(Statement stmt) throws SQLException {
        System.out.println("------ SELECT test begin -------");
        HashMap<String, String> map = new HashMap<>();
        String sql = "SELECT * FROM user order by id";
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String id = rs.getString("id");
            String name = rs.getString("user_name");
            map.put(id, name);
            System.out.println("id: " + id + " user_name: " + name);
        }
        System.out.println("------ SELECT test end -------");
        return map;
    }
}
